Structured Query Language (SQLd is the database access language most commonly used to access relational databases (such as the DB2 product sold by IBM Canada Ltd.) in an open, heterogeneous environment. Although this disclosure refers to the DB2 relational database product sold by IBM, individuals skilled in the art will recognize that the caching of database access statements is applicable to any relational database management system (RDBMS).
Within this specification including the claims, the following terms will be used:
Access Plan An access plan is the method(s) chosen by the SQL Compiler to satisfy an application request as stated in the form of an SQL statement. PA1 Agent A process used by a RDBMS to provide services for an application request. PA1 Node A node is a physical entity (eg. a processor and memory) that is used to process application requests to the RDBMS and contains some or all of the database. A serial version of the RDBMS contains at most 1 node, while a parallel version of the RDBMS can contain 1 or more nodes. PA1 Package A package is associated with an application and contains the information required by the RDBMS for all SQL statements defined in that application. The information in a package consists of a collection of sections and the compilation environment settings (e.g. compile or binding options) used to compile any static SQL statements; some of these same settings are also used as the default environment for any dynamic SQL statements compiled by the application during execution. PA1 Section A section contains all the information required by the RDBMS to execute the chosen access plan for an SQL statement. A section is the compiled version of the access plan chosen by the SQL compiler. PA1 Section Entry A section entry contains information about a specific section as well as the SQL statement corresponding to that section.
There are two basic types of SQL statements, static and dynamic. In using static SQL the user embeds SQL requests for data in an application program. An SQL precompiler removes these statements from the application program and replaces them with function calls whose parameters indicate a specific section entry for the package corresponding to the current source file. The removed SQL statement is then sent to DB2 for compilation. Compiling (also known as preparing) a SQL statement is the process by which the DB2 SQL compiler chooses and builds an access plan to efficiently resolve the SQL statement. The access plan is saved in its executable format, a section, in the system catalogues.
The parsing of the statement and building of the access plan can be relatively long and complicated. Compilation of static SQL improves run time performance by building the access plan before the application is executed.
Dynamic SQL is generally used for ad hoc SQL requests. For example, in a database used to track sales of individual products, a dynamic SQL query may be invoked to list the top ten products sold, by sales region. Depending upon the nature of a dynamic SQL request, the time required to parse it and create an access plan to satisfy the user request can be significant. Further, if the dynamic SQL request is repeated later in the application by the same agent or perhaps by a different agent, a new access plan must be created in each instance. Thus, the creation of an identical access plan may often have to be repeated, thereby impacting performance of the application.
Solutions to improve application performance include the concept of caching all the information necessary for an agent to invoke an access plan.
IBM Technical Disclosure Bulletin volume 39 No. Feb. 2, 1996 pages 113-116 discloses a caching scenario within a client/server environment which applies to a applications running on a single client and using a single interface (the one providing the cache). The present invention is a server cache available to all application requests regardless of interface or client.
IBM Technical Disclosure Bulletin volume 39 No. Feb. 2, 1996 page 179 discusses the concept of caching dynamic SQL statements. This one page synopsis of the idea does not address the issue of multiple applications with multiple agents being able to share the cached SQL statement IBM Technical Disclosure Bulletin volume 39 No. Feb. 2, 1996 at pages 235-236 discloses a concept known in the art as "extended dynamic SQL". Extended dynamic SQL is a method which allows users to specify that dynamic SQL statements relating to a specific package be "captured" and stored in the system catalogues, in effect converting the statements to static SQL. There is no concept of sharing these statements beyond the package with which they are associated. As users of the same package may not be aware that a dynamic SQL statement has been "captured", program logic is required to detect multiple prepare attempts for the same statement and to map it to an existing "captured" statement. Such a scenario requires the user to pro-actively register their queries as well as requiring that all such queries be persistent, i.e. are permanently stored within the database.
In the prior art caching solutions described above, there is no facility to allow agents of multiple applications to access and execute common sections. Thus, there is a need for such a facility.